import xarray as xr
import pandas as pd
import hvplot.pandas
import hvplot.xarray

combined_data_file = "combined_positions.csv"
combined_data_file_depth = "combined_positions_depth.csv"

etopo1_full = xr.open_dataset("data/ETOPO1_Bed_g_gmt4.nc") etopo1_fulletopo1_baltic = etopo1_full.sel( x=slice(9, 30), y=slice(53, 67), ) etopo1_balticetopo1_baltic.to_netcdf("data/ETOPO1_Bed_g_gmt4_baltic.nc")

etopo1_baltic = xr.open_dataset("data/ETOPO1_Bed_g_gmt4_baltic.nc")

df = pd.read_csv(combined_data_file)
df

	Timestamp	Longitude	Latitude	Longitude_	Latitude_	Platform	Type
0	2023-04-24 00:55:07+00:00	10.18159	54.32806	10.20000	54.30000	Littorina	Vessel
1	2023-04-24 01:55:10+00:00	10.18163	54.32806	10.20000	54.30000	Littorina	Vessel
2	2023-04-24 02:55:14+00:00	10.18160	54.32805	10.20000	54.30000	Littorina	Vessel
3	2023-04-24 03:58:09+00:00	10.18160	54.32809	10.20000	54.30000	Littorina	Vessel
4	2023-04-24 04:58:10+00:00	10.18161	54.32804	10.20000	54.30000	Littorina	Vessel
...	...	...	...	...	...	...	...
13749	2023-05-09 08:40:47+00:00	10.73319	54.93622	10.73319	54.93622	D303	Buoy
13750	2023-05-09 08:45:46+00:00	10.73306	54.93580	10.73306	54.93580	D303	Buoy
13751	2023-05-09 09:20:47+00:00	10.73313	54.93616	10.73313	54.93616	D303	Buoy
13752	2023-05-09 09:25:48+00:00	10.73306	54.93605	10.73306	54.93605	D303	Buoy
13753	2023-05-09 09:40:44+00:00	10.73317	54.93613	10.73317	54.93613	D303	Buoy

13754 rows × 7 columns

df["depth"] = - etopo1_baltic.z.sel(
    x=df[["Longitude"]].to_xarray()["Longitude"],
    y=df[["Latitude"]].to_xarray()["Latitude"],
    method="nearest",
).to_dataframe()["z"]

df

	Timestamp	Longitude	Latitude	Longitude_	Latitude_	Platform	Type	depth
0	2023-04-24 00:55:07+00:00	10.18159	54.32806	10.20000	54.30000	Littorina	Vessel	-17.0
1	2023-04-24 01:55:10+00:00	10.18163	54.32806	10.20000	54.30000	Littorina	Vessel	-17.0
2	2023-04-24 02:55:14+00:00	10.18160	54.32805	10.20000	54.30000	Littorina	Vessel	-17.0
3	2023-04-24 03:58:09+00:00	10.18160	54.32809	10.20000	54.30000	Littorina	Vessel	-17.0
4	2023-04-24 04:58:10+00:00	10.18161	54.32804	10.20000	54.30000	Littorina	Vessel	-17.0
...	...	...	...	...	...	...	...	...
13749	2023-05-09 08:40:47+00:00	10.73319	54.93622	10.73319	54.93622	D303	Buoy	-3.0
13750	2023-05-09 08:45:46+00:00	10.73306	54.93580	10.73306	54.93580	D303	Buoy	-3.0
13751	2023-05-09 09:20:47+00:00	10.73313	54.93616	10.73313	54.93616	D303	Buoy	-3.0
13752	2023-05-09 09:25:48+00:00	10.73306	54.93605	10.73306	54.93605	D303	Buoy	-3.0
13753	2023-05-09 09:40:44+00:00	10.73317	54.93613	10.73317	54.93613	D303	Buoy	-3.0

13754 rows × 8 columns

df.to_csv(combined_data_file_depth, index=False)
!head -n5 {combined_data_file_depth}

Timestamp,Longitude,Latitude,Longitude_,Latitude_,Platform,Type,depth
2023-04-24 00:55:07+00:00,10.18159,54.32806,10.2,54.3,Littorina,Vessel,-17.0
2023-04-24 01:55:10+00:00,10.18163,54.32806,10.2,54.3,Littorina,Vessel,-17.0
2023-04-24 02:55:14+00:00,10.1816,54.32805,10.2,54.3,Littorina,Vessel,-17.0
2023-04-24 03:58:09+00:00,10.1816,54.32809,10.2,54.3,Littorina,Vessel,-17.0